package org.example.service.impl;

import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.excel.EasyExcel;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.example.entity.Order;
import org.example.entity.OrderDetailExample;
import org.example.entity.OrderExample;
import org.example.exception.OrderHandlerException;
import org.example.mapper.OrderDetailMapper;
import org.example.mapper.OrderMapper;
import org.example.service.IOrderService;
import org.example.web.form.OrderQueryForm;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

@Service
public class OrderServiceImpl implements IOrderService {
    @Resource
    private OrderMapper orderMapper;
    @Resource
    private OrderDetailMapper orderDetailMapper;

    @Override
    public PageInfo<Order> selectByPage(OrderQueryForm orderQueryForm, int page, int limit) {
        PageHelper.startPage(page,limit);
//        OrderExample orderExample = new OrderExample();
//        orderExample.setOrderByClause("createtime desc");
//        List<Order> orderList = orderMapper.selectByExample(orderExample);
//        List<Order> orderList = orderMapper.selectOrderRelationUserInfo();

        List<Order> orderList = orderMapper.selectByUserIdOrCreatetime(orderQueryForm);
        return new PageInfo<Order>(orderList);
    }

    @Override
    public Order info(Integer orderId) {
        return orderMapper.selectByPrimaryKey(orderId);
    }

    @Override
    public int updateById(Order order) {
        return orderMapper.updateByPrimaryKeySelective(order);
    }

    @Transactional
    public int deleteById(Integer orderId) {
        return deleteOrderRelationDetail(new Integer[]{orderId});
    }

    @Transactional
    public int deleteBatch(Integer[] ids) {
        return deleteOrderRelationDetail(ids);
    }

    @Override
    public int save(Order order) {
        order.setCreatetime(new Date());//设置系统时间
        return orderMapper.insertSelective(order);
    }

    /**
     * 【废弃】已经与list合并成一个方法
     * @param orderQueryForm 查询条件
     * @param page  页数
     * @param limit 每页条数
     * @return
     */
    @Override
    public PageInfo<Order> search(OrderQueryForm orderQueryForm, int page, int limit) {
        //分页
        PageHelper.startPage(page,limit);
        List<Order> orderList = orderMapper.selectByUserIdOrCreatetime(orderQueryForm);
        PageInfo<Order> orderPageInfo = new PageInfo<>(orderList);
        return orderPageInfo;
    }



    @Override
    public void export(int page, HttpServletResponse response) {
        PageHelper.startPage(page,3);
        List<Order> orderList = orderMapper.selectByExample(null);
        //TODO 创建execl文件对象
        XSSFWorkbook wb = new XSSFWorkbook();
        //设置样式
        XSSFCellStyle cellStyle = wb.createCellStyle();

        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 单元格文本水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 单元格文本垂直居中
        cellStyle.setBorderBottom(BorderStyle.MEDIUM);
        cellStyle.setBorderLeft(BorderStyle.MEDIUM);
        cellStyle.setBorderRight(BorderStyle.MEDIUM);
        cellStyle.setBorderTop(BorderStyle.MEDIUM);
        //TODO 创建sheet
        Sheet sheet = wb.createSheet("orders");// 创建一张表
        //TODO  行row
        Row topRow = sheet.createRow(0);// 创建第一行，起始为0
        //TODO 列cell
        Cell cell1 = topRow.createCell(0);
        cell1.setCellStyle(cellStyle);
        cell1.setCellValue("序号");
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 4);
        sheet.addMergedRegion(region);
//        sheet.setHorizontallyCenter(true);

        Row titleRow = sheet.createRow(1);// 创建第一行，起始为0
        //TODO 列cell

        Cell cell0 = titleRow.createCell(0);
        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("编号");

        titleRow.createCell(1).setCellValue("用户id");// 第一列
        titleRow.createCell(2).setCellValue("数量");
        titleRow.createCell(3).setCellValue("时间");
        titleRow.createCell(4).setCellValue("备注");

        //TODO 填充数据------
        for (int i = 0; i < orderList.size(); i++) {
            Row DataRow = sheet.createRow(2+i);// 创建第一行，起始为0

            DataRow.createCell(0).setCellValue(orderList.get(i).getId());
            DataRow.createCell(1).setCellValue(orderList.get(i).getUserId());// 第一列
            DataRow.createCell(2).setCellValue(orderList.get(i).getNumber());

            DataRow.createCell(3).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(orderList.get(i).getCreatetime()));
            DataRow.createCell(4).setCellValue(orderList.get(i).getNote());
        }

        //TODO 创建excel文件流
        String fileName = "订单信息报表.xlsx";
        OutputStream outputStream = null;
        try {
            // 7.下载的文件的名字的编码为utf-8
            fileName = URLEncoder.encode(fileName, "UTF-8");
            // 设置ContentType请求信息格式 响应的内容格式
            // 8.输出数据类型--mima类型：参考：https://www.w3school.com.cn/media/media_mimeref.asp
            response.setContentType("application/vnd.ms-excel");
            // 9.控制用户请求所得的内容存为一个文件的时候提供一个默认的文件名，文件直接在浏览器上显示或者在访问时弹出文件下载对话框。
            // 参考：https://www.cnblogs.com/wq-9/articles/12165056.html
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            // 10、通过response输出流输出excel对象
            outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private int deleteOrderRelationDetail(Integer[] ids) {
        //1.先删除从  orderdetail  delete from `orderdetail` where order_id in(1,2,3,4)
        OrderDetailExample orderDetailExample = new OrderDetailExample();
        OrderDetailExample.Criteria criteria = orderDetailExample.createCriteria();
        criteria.andOrderIdIn(Arrays.asList(ids));//List list = Arrays.asList(数组)
        int detailRows = orderDetailMapper.deleteByExample(orderDetailExample);
        if(detailRows<=0){
            throw new OrderHandlerException("删除订单详细失败");
        }
        //2.再删除主 order  delete from `order` where id in (1,2,3,4)
        OrderExample orderExample =new OrderExample();
        OrderExample.Criteria criteria1 = orderExample.createCriteria();
        criteria1.andIdIn(Arrays.asList(ids));
        int orderRows = orderMapper.deleteByExample(orderExample);
        if(orderRows<=0){
            throw new OrderHandlerException("删除订单失败");
        }
        return orderRows;
    }

    @Override
    public void exportHutool(int page, HttpServletResponse response) {
        PageHelper.startPage(page,3);
        List<Order> orderList = orderMapper.selectByExample(null);
        //在内存操作，写到浏览器
        ExcelWriter writer= ExcelUtil.getWriter(true);

        // 自定义标题别名
        writer.addHeaderAlias("id","订单编号");
        writer.addHeaderAlias("userId","用户编号");
        writer.addHeaderAlias("number","数量");
        writer.addHeaderAlias("createtime","下单时间");
        writer.addHeaderAlias("note","备注");

        //默认配置
        writer.write(orderList,true);
        //设置content—type
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
       try {
           //设置标题
           String fileName= URLEncoder.encode("用户信息","UTF-8");
           //Content-disposition是MIME协议的扩展，MIME协议指示MIME用户代理如何显示附加的文件。
           response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
           ServletOutputStream outputStream= response.getOutputStream();

           //将Writer刷新到OutPut
           writer.flush(outputStream,true);
           outputStream.close();
           writer.close();
       }catch (Exception e){
           e.printStackTrace();
       }

    }
    @Override
    public void exportEasyExecl(int page, HttpServletResponse response) {
        PageHelper.startPage(page,3);
        List<Order> orderList = orderMapper.selectByExample(null);
        //TODO 设置编码和内容类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        try {
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("订单数据", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), Order.class).sheet("模板").doWrite(orderList);
        }catch (Exception e){
            e.printStackTrace();;
        }

    }


    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel
    /**
     *
     * @param multipartFile 包含上传文件所有信息的对象
     */

    @Override
    public String importEasyExecl(MultipartFile multipartFile) {
        List<Order> orderList = new ArrayList<>();
        try{
            //获取上传的文件的文件io流
            InputStream is = multipartFile.getInputStream();
            //1.根据上传文件信息，构建excel文件  ，multipartFile.getOriginalFilename()获取上传文件的名称
            Workbook workBook = this.getWorkbook(is, multipartFile.getOriginalFilename());
            //2.获取execl第1个sheet
            Sheet sheet = workBook.getSheetAt(0);

            if (sheet != null){
                //3.获取每一行的每一列数据
                for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++){
                    Row row = sheet.getRow(i);//第i行
                    Order order = new Order();//每一行对应一个order对象
                    for (int j = 1; j < row.getPhysicalNumberOfCells(); j++){
                        Cell cell = row.getCell(j);//第j列
                        cell.setCellType(CellType.STRING);//避免日期为：29-六月-2020  和整数加0
                        String cellValue = cell.toString();
//                        System.out.print("【"+cellStr+"】 ");
                        CellType cellType = cell.getCellType();
                        switch (j){
                            case 2:
                                if(cellValue.indexOf(".")!=-1){//hutool 与 easyexcel导出的数字变为小数
                                    order.setUserId(Integer.parseInt(cellValue.substring(0,cellValue.indexOf("."))));
                                    break;
                                }
                                order.setUserId(Integer.parseInt(cellValue));//poi数字不会变为小数
                                break;
                            case 3:
                                order.setNumber(cellValue);
                                break;
                            case 4:
                                //日期作为字符串读取为444.111，需要444.111将转为Date
                                order.setCreatetime(org.apache.poi.ss.usermodel.DateUtil
                                        .getJavaDate(Double.parseDouble(cellValue)));
                                break;
                            default:
                                order.setNote(cellValue);
                                break;

                        }

                    }
                    orderList.add(order);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        orderList.forEach(System.out::println);
        //进行批量添加-------自行完成
        return "success";
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if(excel2003L.equals(fileType)){
            wb = new HSSFWorkbook(inStr);  //2003-
        }else if(excel2007U.equals(fileType)){
            wb = new XSSFWorkbook(inStr);  //2007+
        }else{
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }
}
